package dao

import (
	"errors"
	"fmt"
	"time"
	"yundian/internel/app/helper"
	"yundian/internel/app/helper/request"
	"yundian/internel/app/models/automate"
	"yundian/internel/app/models/users"
	"yundian/internel/pkg/dbs"
	"yundian/internel/pkg/dingo"
	"yundian/internel/pkg/dingo/global"
)

type AutomateDao struct {
}

func NewAutomateDaoDao() *AutomateDao {
	return &AutomateDao{}
}

// 查询可提现商品订单
func (dao *AutomateDao) GetAutomateOrdersToCashByUserId(param *request.OrdersCashReq) *dingo.ErrorWrap {
	dbData := &request.OrdersCashRes{}

	db := global.Orm.Table("automate_dividends_log")
	db.Select(`SUM(amount)cash`).Where("user_id=?", param.UserId)
	db.Find(&dbData)

	return dingo.ErrorIf(db.Error, dbData)
}

// 员工查询售货机/订单信息
func (d *AutomateDao) GetStaffAutomateOrdersDetails(param *request.GetStaffAutomateOrdersDetailsReq) *dingo.ErrorWrap {
	dbInfo := &request.GetStaffAutomateOrdersDetailsRes{}

	fields := `ao.*,d.bluetooth_mac`
	db := global.Orm.Table("automate_orders ao").Select(fields)
	db.Joins("LEFT JOIN devices d ON ao.device_sn=d.device_sn")

	if param.DeviceSn != "" {
		db.Where("ao.device_sn=?", param.DeviceSn)
	}
	if param.NotificationId > 0 {
		db.Where("ao.notification_id=?", param.NotificationId)
	}

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

func (d *AutomateDao) GetInventoryLogList(param *request.GetAutomateInventoryLogListListReq) *dingo.ErrorWrap {
	var (
		lst    []*request.GetAutomateInventoryLogListListRes
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `agil.*,p.place_name,DATE_FORMAT(agil.create_time, '%Y-%m-%d %T')create_time,
	DATE_FORMAT(agil.update_time,'%Y-%m-%d %T')update_time,u.user_name`
	db := global.Orm.Table("automate_goods_inventory_log agil").Select(fields)
	db.Joins("LEFT JOIN users u ON agil.user_id = u.id")
	db.Joins("LEFT JOIN places p ON agil.place_id = p.id")

	if param.PlaceId > 0 {
		db.Where("agil.place_id=?", param.PlaceId)
	}
	if param.UserName != "" {
		db.Where("u.user_name like ?", fmt.Sprint("%", param.UserName, "%"))
	}

	db.Order("agil.id DESC").Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *AutomateDao) SetAllGoodsInventoryNum(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	sql := fmt.Sprintf(`UPDATE automate_goods SET num=num+%d WHERE id=?`, params.Db.Num)
	db := global.Orm.Exec(sql, params.Db.AutomateGoodsId)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) SetPlaceGoodsInventoryNum(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	if params.Db.PlaceId == 0 {
		return d.SetAllGoodsInventoryNum(params)
	}

	sql := fmt.Sprintf(`UPDATE automate_place_goods_bind SET num=num+%d WHERE automate_goods_id=? AND place_id=?`, params.Db.Num)
	db := global.Orm.Exec(sql, params.Db.AutomateGoodsId, params.Db.PlaceId)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertAllGoodsInventoryLog(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	sql := fmt.Sprintf(`INSERT INTO automate_goods_inventory_log (place_id,automate_goods_id,old_num, 
	new_num, user_id, automate_goods_name, automate_goods_icon, message)SELECT place_id,id,num-%d,num,%d,
	ag.name,ag.icon,'' FROM automate_goods ag WHERE ag.id=?`, params.Db.Num, params.LoginUserId)
	db := global.Orm.Exec(sql, params.Db.AutomateGoodsId)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertPlaceGoodsInventoryLog(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	if params.Db.PlaceId == 0 {
		return d.InsertAllGoodsInventoryLog(params)
	}

	sql := fmt.Sprintf(`INSERT INTO automate_goods_inventory_log (place_id, automate_goods_id, old_num, new_num, 
	user_id, automate_goods_name, automate_goods_icon, message)SELECT apgb.place_id,apgb.automate_goods_id,apgb.num-%d,
	apgb.num,%d,ag.name,ag.icon,'' FROM automate_place_goods_bind apgb LEFT JOIN automate_goods ag ON 
	apgb.automate_goods_id=ag.id WHERE apgb.automate_goods_id=? AND apgb.place_id=?`, params.Db.Num, params.LoginUserId)
	db := global.Orm.Exec(sql, params.Db.AutomateGoodsId, params.Db.PlaceId)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) DeletePlaceGoods(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	db := global.Orm.Model(&params.Db).Where("place_id=?", params.Db.PlaceId).Delete("id=?", params.Db.Id)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) UpdatePlaceGoods(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	dbUpdate := map[string]interface{}{
		"warning_num": params.Db.WarningNum,
		"status":      params.Db.Status,
		"message":     params.Db.Message,
	}
	db := global.Orm.Model(&params.Db).Where("id=?", params.Db.Id).Updates(dbUpdate)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertPlaceGoods(params *request.PlaceGoodsReq) *dingo.ErrorWrap {
	db := global.Orm.Create(&params.Db)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) GetPlaceGoodsDeviceNumList(ids []int) *dingo.ErrorWrap {
	var lst []*request.GetAutomatePlaceGoodsNumRes

	fields := `COUNT(1)num,SUM(IF(status=1,1,0))load_num,place_id,automate_goods_id`
	db := global.Orm.Table("automate_goods_bind agb").Select(fields)
	db.Where("place_id in ?", ids).Group("place_id,automate_goods_id").Find(&lst)
	return dingo.ErrorIf(db.Error, lst)
}

func (d *AutomateDao) GetPlaceGoodsOrderNumList(ids []int) *dingo.ErrorWrap {
	var lst []*request.GetAutomatePlaceGoodsNumRes

	fields := `COUNT(1)num,place_id,automate_good_id automate_goods_id,place_name,goods_name`
	db := global.Orm.Table("automate_orders ao").Select(fields)
	db.Where("place_id in ?", ids).Where("status=?", helper.TypeNumOrderAutomateStatusWaitDone)
	db.Group("place_id,automate_good_id").Find(&lst)
	return dingo.ErrorIf(db.Error, lst)
}

func (d *AutomateDao) GetPlaceGoodsInventoryLogList(ids []int) *dingo.ErrorWrap {
	var lst []*request.GetAutomatePlaceGoodsNumRes

	fields := `SUM(new_num)-SUM(old_num)num,place_id,automate_goods_id`
	db := global.Orm.Table("automate_goods_inventory_log agil").Select(fields)
	db.Where("place_id in ?", ids).Group("place_id,automate_goods_id").Find(&lst)
	return dingo.ErrorIf(db.Error, lst)
}

func (d *AutomateDao) GetPlaceGoodsList(param *request.GetAutomateGoodsListReq) *dingo.ErrorWrap {
	var (
		lst    []*request.GetAutomatePlaceGoodsListRes
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `ag.*,p.place_name,DATE_FORMAT(apgb.create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT
	(apgb.update_time,'%Y-%m-%d %T')update_time,apgb.warning_num,apgb.num,apgb.status 
	place_goods_status,apgb.message,apgb.id,apgb.automate_goods_id,apgb.place_id`
	db := global.Orm.Table("automate_place_goods_bind apgb").Select(fields)
	db.Joins("LEFT JOIN automate_goods ag ON ag.id=apgb.automate_goods_id")
	db.Joins("LEFT JOIN places p ON apgb.place_id=p.id")
	db.Where("ag.status<>? AND ag.id>0", helper.TypeNumGoodsStatusDelete)

	if param.Status != 0 {
		db.Where("ag.status=?", param.Status)
	}
	if param.PlaceId > 0 {
		db.Where("apgb.place_id=?", param.PlaceId)
	}

	if param.WarningOrder == 1 {
		db.Order("(cast(apgb.num as signed) - cast(apgb.warning_num as signed))")
	} else {
		db.Order("ag.id DESC")
	}

	db.Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *AutomateDao) GetAutomateDividendsList(tradeNo string) *dingo.ErrorWrap {
	var lst []*request.GetAutomateDividendsListRes

	fields := `adl.*,ao.amount order_amount, DATE_FORMAT(adl.create_time, '%Y-%m-%d %T')create_time,
	DATE_FORMAT(adl.update_time,'%Y-%m-%d %T')update_time,DATE_FORMAT(adl.end_date, '%Y-%m-%d')end_date`
	db := global.Orm.Table("automate_dividends_log adl").Select(fields)
	db.Joins("LEFT JOIN automate_orders ao ON adl.trade_no=ao.trade_no")

	db.Where("adl.trade_no=?", tradeNo)

	db.Find(&lst)
	return dingo.ErrorIf(db.Error, lst)
}

func (d *AutomateDao) InsertAutomateOrderDividends(dbList []*automate.DividendsLogDb) error {
	db := global.Orm.Create(&dbList)
	return db.Error
}

// 查询待分红列表
func (d *AutomateDao) GetAutomateOrderWaitDividends(tradeNo string, isOpenFirst int) []*request.GetAdminAutomateOrderWaitDividendsRes {
	var lst []*request.GetAdminAutomateOrderWaitDividendsRes

	fields := `aour.*,ao.device_sn,ao.amount,ao.place_id,ao.place_name,u.parent_id,u.user_name,u.sm_profit_proportion,u.is_dividends_sm,u.is_admin`
	db := global.Orm.Table("automate_orders_user_relevancy aour").Select(fields)
	db.Joins("LEFT JOIN users u ON aour.user_id = u.id")
	db.Joins("LEFT JOIN automate_orders ao ON ao.trade_no=aour.trade_no")
	db.Where("u.is_dividends_sm<>2 AND ao.status=? AND aour.trade_no=?", helper.TypeNumOrderAutomateStatusWaitDone, tradeNo)
	if isOpenFirst == 1 {
		db.Where("u.is_admin != 3")
	}
	db.Find(&lst)
	return lst
}

func (d *AutomateDao) GetAdminAutomateOrderInfoByTradeNo(tradeNo string) *dingo.ErrorWrap {
	dbInfo := &automate.OrderModel{}

	fields := `ao.*,DATE_FORMAT(ao.create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT(ao.update_time,'%Y-%m-%d %T')
	update_time,DATE_FORMAT(ao.pay_time, '%Y-%m-%d %T')pay_time,DATE_FORMAT(ao.logistics_time, '%Y-%m-%d %T')logistics_time`
	db := global.Orm.Table("automate_orders ao").Select(fields)
	db.Where("ao.trade_no=?", tradeNo)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetOrderFirstByPlaceIdAndDeviceSn 查看是否为首单
func (d *AutomateDao) GetOrderFirstByPlaceIdAndDeviceSn(placeId int, deviceSn string) *automate.OrderWithPlaceFirst {
	data := &automate.OrderWithPlaceFirst{}

	sql := `SELECT p.is_open_first,a.id,a.place_id,a.device_sn FROM places p 
RIGHT JOIN (SELECT place_id,id,device_sn FROM automate_orders WHERE place_id = ? and device_sn = ? 
ORDER BY id ASC LIMIT 1) a ON p.id = a.place_id `

	global.Orm.Raw(sql, placeId, deviceSn).Find(&data)
	return data
}

func (d *AutomateDao) GetAdminAutomateOrderList(param *request.GetAutomateOrderListReq) *dingo.ErrorWrap {
	var (
		lst    []*automate.OrderModel
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `ao.*,DATE_FORMAT(ao.create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT(ao.update_time,'%Y-%m-%d %T')
	update_time,DATE_FORMAT(ao.pay_time, '%Y-%m-%d %T')pay_time,DATE_FORMAT(ao.logistics_time, '%Y-%m-%d %T')logistics_time`
	db := global.Orm.Table("automate_orders ao").Select(fields)
	db.Joins("LEFT JOIN automate_orders_user_relevancy aour ON ao.id = aour.id")

	db.Where("aour.user_id=?", param.LoginUserId)
	if param.Status != 0 {
		db.Where("ao.status=?", param.Status)
	}
	if param.RoutesId != 0 {
		db.Where("ao.routes_id=?", param.RoutesId)
	}
	if param.PlaceId > 0 {
		db.Where("ao.place_id=?", param.PlaceId)
	}
	if param.DeviceSn != "" {
		db.Where("ao.device_sn=?", param.DeviceSn)
	}
	if param.TradeNo != "" {
		db.Where("ao.trade_no=?", param.TradeNo)
	}

	db.Order("ao.id DESC").Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *AutomateDao) GetWxAutomateOrderList(param *request.GetAutomateOrderListReq) *dingo.ErrorWrap {
	var (
		lst    []*automate.OrderModel
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `ao.*,DATE_FORMAT(ao.create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT(ao.update_time,'%Y-%m-%d %T')
	update_time,DATE_FORMAT(ao.pay_time, '%Y-%m-%d %T')pay_time,DATE_FORMAT(ao.logistics_time, '%Y-%m-%d %T')logistics_time`
	db := global.Orm.Table("automate_orders ao").Select(fields)
	//db.Joins("LEFT JOIN automate_orders_user_relevancy aour ON ao.id = aour.id")

	db.Where("ao.member_id=?", param.MemberId)
	if param.Status != 0 {
		db.Where("ao.status=?", param.Status)
	}
	if param.RoutesId != 0 {
		db.Where("ao.routes_id=?", param.RoutesId)
	}
	if param.PlaceId > 0 {
		db.Where("ao.place_id=?", param.PlaceId)
	}
	if param.DeviceSn != "" {
		db.Where("ao.device_sn=?", param.DeviceSn)
	}

	db.Order("ao.id DESC").Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *AutomateDao) GetGoodsOrderInfoByTradeNo(tradeNo string) *dingo.ErrorWrap {
	dbInfo := &request.GetGoodsOrderInfoByIdRes{}
	db := global.Orm.Table("automate_orders ao").Select("ao.*,m.phone,d.room")
	db.Joins("LEFT JOIN members m ON ao.member_id=m.id")
	db.Joins("LEFT JOIN devices d ON d.device_sn=ao.device_sn")
	db.Where("ao.trade_no=?", tradeNo).Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

func (d *AutomateDao) UpdateGoodsOrder(params *automate.OrderPayDb) *dingo.ErrorWrap {
	tx, key := dbs.GormBegin("UpdateGoodsOrder")

	dbInfo := map[string]interface{}{}
	if params.Status > 0 {
		dbInfo["status"] = params.Status
	}
	if params.PayTime != "" {
		dbInfo["pay_time"] = params.PayTime
	}
	if params.ThirdTradeNo != "" {
		dbInfo["third_trade_no"] = params.ThirdTradeNo
	}
	if params.NotificationId > 0 {
		dbInfo["notification_id"] = params.NotificationId
	}

	if params.Status == helper.TypeNumOrderAutomateStatusWaitDone {
		dbInfo["logistics_time"] = time.Now().Format("2006-01-02 15:04:05")
		sql := `UPDATE automate_goods_bind agb LEFT JOIN automate_orders ao ON agb.device_sn
		=ao.device_sn AND agb.routes_id=ao.routes_id SET agb.status=2 WHERE AO.trade_no=?`
		if db := tx.Exec(sql, params.TradeNo); db.Error != nil {
			dbs.GormRollback(tx, key, db.Error)
			return dingo.ErrorIf(db.Error)
		}
		numSql := `UPDATE automate_place_goods_bind apgb LEFT JOIN automate_orders ao ON apgb.automate_goods_id=
		ao.automate_good_id AND apgb.place_id=ao.place_id SET num=IF(num>0,num-1,0) WHERE ao.trade_no=?`
		if db := tx.Exec(numSql, params.TradeNo); db.Error != nil {
			dbs.GormRollback(tx, key, db.Error)
			return dingo.ErrorIf(db.Error)
		}
	}
	if params.Status == helper.TypeNumOrderAutomateStatusWaitRevocation {
		if db := tx.Exec("DELETE FROM automate_dividends_log WHERE trade_no=?", params.TradeNo); db.Error != nil {
			dbs.GormRollback(tx, key, db.Error)
			return dingo.ErrorIf(db.Error)
		}
	}

	params.CreateTime = time.Now().Format("2006-01-02 15:04:05")
	if db := tx.Model(&params).Where("trade_no=?", params.TradeNo).Updates(dbInfo); db.Error != nil {
		dbs.GormRollback(tx, key, db.Error)
		return dingo.ErrorIf(db.Error)
	}
	dbs.GormCommit(tx, key)
	return dingo.ErrorIf(nil)
}

func (d *AutomateDao) InsertGoodsOrderRelevancy(params *automate.OrderDb) *dingo.ErrorWrap {
	//userInfo := NewUserDao().GetUserInfoById(params.UserId).Unwrap().(*users.UserModel)
	userList := NewUserDao().QueryParentList(params.UserId, 0).Unwrap().([]*users.UserModel)
	var dbList []*automate.OrderUserRelevancyModel
	userKey := map[int]bool{}
	isAdminId := false
	for _, item := range userList {
		if item.IsAdmin == helper.TypeNumIsAdminOperator {
			isAdminId = true
		}

		if userKey[item.ID] {
			continue
		}
		userKey[item.ID] = true
		dbList = append(dbList, &automate.OrderUserRelevancyModel{
			UserId:   item.ID,
			Id:       params.Id,
			TradeNo:  params.TradeNo,
			EndDate:  params.CreateTime[:10],
			EndMonth: params.CreateTime[:7],
		})
	}

	if !isAdminId {
		return dingo.ErrorIf(errors.New("商户配置错误"))
	}

	db := global.Orm.Create(&dbList)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertGoodsOrder(params *automate.OrderDb) *dingo.ErrorWrap {
	params.CreateTime = time.Now().Format("2006-01-02 15:04:05")
	if db := global.Orm.Create(&params); db.Error != nil {
		return dingo.ErrorIf(db.Error)
	}

	db := d.InsertGoodsOrderRelevancy(params)
	return dingo.ErrorIf(db.Err)
}

func (d *AutomateDao) DeleteGoodsBind(params *request.InitGoodsBindReq) *dingo.ErrorWrap {
	db := global.Orm.Model(&params.Db).Delete("id=?", params.Db.Id)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) UpdateGoodsBindStatus(params *request.InitGoodsBindReq) *dingo.ErrorWrap {
	db := global.Orm.Model(&params.Db).Where("id=?", params.Db.Id).Update("status", params.Db.Status)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) UpdateGoodsBindStatusByDeviceSn(params *request.HandleUpdateAutomateGoodsBindStatusReq) *dingo.ErrorWrap {
	db := global.Orm.Table("automate_goods_bind").Where("device_sn=? AND routes_id=?", params.DeviceSn, params.RoutesId)
	db.Update("status", params.Status)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) UpdateGoodsBind(params *request.InitGoodsBindReq) *dingo.ErrorWrap {
	db := global.Orm.Where("id=?", params.Db.Id).Updates(&params.Db)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertGoodsBind(params *request.InitGoodsBindReq) *dingo.ErrorWrap {
	param := params.Db
	whereList := []interface{}{param.PlaceId}
	where := `place_id=?`
	if params.Db.DeviceSn != "" {
		where += ` AND device_sn=?`
		whereList = append(whereList, param.DeviceSn)
	}

	sql := fmt.Sprintf(`INSERT INTO automate_goods_bind (place_id,automate_goods_id,status,routes_id,device_sn,message) 
	(SELECT place_id, %d automate_goods_id,%d status,%d routes_id,device_sn,'%s' message FROM devices WHERE %s ) ON DUPLICATE KEY 
	UPDATE place_id=VALUES(place_id),status=VALUES(status)`, param.AutomateGoodsId, param.Status, param.RoutesId, param.Message, where)
	db := global.Orm.Exec(sql, whereList...)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) GetAutomateGoodsBindList(param *request.GetAutomateGoodsBindListReq) *dingo.ErrorWrap {
	var (
		lst    []*request.GetAutomateGoodsBindListRes
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `agb.*,p.place_name,p.user_id,DATE_FORMAT(agb.create_time, '%Y-%m-%d %T')create_time,
	DATE_FORMAT(agb.update_time,'%Y-%m-%d %T')update_time,ag.name,ag.status goods_status,ag.price,apgb.num,ag.icon`
	db := global.Orm.Table("automate_goods_bind agb").Select(fields)
	db.Joins("LEFT JOIN automate_goods ag ON ag.id=agb.automate_goods_id")
	db.Joins("LEFT JOIN automate_place_goods_bind apgb ON ag.id=apgb.automate_goods_id AND agb.place_id=apgb.place_id")
	db.Joins("LEFT JOIN places p ON p.id=agb.place_id")
	db.Where("ag.status<>?", helper.TypeNumGoodsStatusDelete)

	if param.LoginUserId > 0 {
		if param.IsAdmin == helper.TypeNumIsAdminPartner {
			userIdArr := NewUserDao().QueryChildUserIds(param.LoginUserId).Unwrap().([]int)
			db.Where("p.user_id IN ?", userIdArr)
		} else {
			db.Where("p.user_id=?", param.LoginUserId)
		}
	}
	if param.GoodsStatus != 0 {
		db.Where("ag.status=?", param.GoodsStatus)
	}
	if param.Status != 0 {
		db.Where("agb.status=?", param.Status)
	}
	if param.RoutesId != 0 {
		db.Where("agb.routes_id=?", param.RoutesId)
	}
	if param.PlaceId > 0 {
		db.Where("agb.place_id=?", param.PlaceId)
	}
	if param.DeviceSn != "" {
		db.Where("agb.device_sn=?", param.DeviceSn)
	}

	db.Order("device_sn DESC,agb.routes_id").Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

// GetGoodsBindCount 查询绑定设备数量
func (d *AutomateDao) GetGoodsBindCount(params *request.InitGoodsReq) *dingo.ErrorWrap {
	var dbCount int64
	db := global.Orm.Table("automate_goods_bind agb").Where("automate_goods_id=?", params.Db.Id)
	//global.Orm.Exec(`DELETE FROM automate_goods_bind WHERE automate_goods_id=?`, params.Db.Id)
	//global.Orm.Exec(`DELETE FROM automate_place_goods_bind WHERE automate_goods_id=?`, params.Db.Id)
	db.Count(&dbCount)
	return dingo.ErrorIf(db.Error, dbCount)
}

func (d *AutomateDao) DeleteGoods(params *request.InitGoodsReq) *dingo.ErrorWrap {
	db := global.Orm.Model(&params.Db).Delete("id=?", params.Db.Id)
	//global.Orm.Exec(`DELETE FROM automate_goods_bind WHERE automate_goods_id=?`, params.Db.Id)
	//global.Orm.Exec(`DELETE FROM automate_place_goods_bind WHERE automate_goods_id=?`, params.Db.Id)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) UpdateGoods(params *request.InitGoodsReq) *dingo.ErrorWrap {
	db := global.Orm.Where("id=?", params.Db.Id).Updates(&params.Db)

	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InsertGoods(params *request.InitGoodsReq) *dingo.ErrorWrap {
	db := global.Orm.Create(&params.Db)
	return dingo.ErrorIf(db.Error)
}

func (d *AutomateDao) InitGoods(params *request.InitGoodsReq) *dingo.ErrorWrap {
	//param := params.Db
	//whereList := []interface{}{param.PlaceId}
	//where := `place_id=?`
	//if params.Db.DeviceSn != "" {
	//	where += ` AND device_sn=?`
	//	whereList = append(whereList, param.DeviceSn)
	//}
	//
	//sql := fmt.Sprintf(`INSERT INTO automate_goods (place_id,price,discount_amount,num,sell_num,status,routes_id,
	//device_sn,icon, img_list, brand_name, name, type_name, message) (SELECT place_id,%d price,%d discount_amount,%d num,
	//0 sell_num,%d status,%d routes_id,device_sn,'%s' icon,'%s' img_list,'%s' brand_name,'%s' name,'%s' type_name,'%s'
	//message FROM devices WHERE %s ) ON DUPLICATE KEY UPDATE place_id=VALUES(place_id),status=VALUES(status)`,
	//	param.Price, param.DiscountAmount, param.Num, param.Status, param.RoutesId, param.Icon, param.ImgList,
	//	param.BrandName, param.Name, param.TypeName, param.Message, where)
	//db := global.Orm.Exec(sql, whereList...)
	//
	//return dingo.ErrorIf(db.Error)
	return dingo.ErrorIf(nil)
}

func (d *AutomateDao) GetAutomateGoodsList(param *request.GetAutomateGoodsListReq) *dingo.ErrorWrap {
	var (
		lst    []*request.GetAutomateGoodsListRes
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `ag.*,p.place_name,DATE_FORMAT(create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT(update_time, '%Y-%m-%d %T')update_time`
	db := global.Orm.Table("automate_goods ag").Select(fields)
	db.Joins("LEFT JOIN places p ON ag.place_id=p.id")
	db.Where("ag.status<>?", helper.TypeNumGoodsStatusDelete)

	if param.Status != 0 {
		db.Where("ag.status=?", param.Status)
	}
	if param.PlaceId > 0 {
		db.Where("place_id=?", param.PlaceId)
	}
	if param.DeviceSn != "" {
		db.Where("device_sn=?", param.DeviceSn)
	}
	if param.Name != "" {
		db.Where("ag.name LIKE ?", "%"+param.Name+"%")
	}

	db.Order("ag.id DESC").Limit(limit).Offset(offset).Find(&lst)
	db.Select("COUNT(1)").Limit(-1).Offset(-1).Find(&count)
	return dingo.ErrorIf(db.Error, lst, count)
}

func (d *AutomateDao) GetAutomateGoodsInfoById(id int) *dingo.ErrorWrap {
	dbInfo := &request.GetAutomateGoodsListRes{}

	fields := `ag.*,p.place_name,DATE_FORMAT(create_time, '%Y-%m-%d %T')create_time,DATE_FORMAT(update_time, '%Y-%m-%d %T')update_time`
	db := global.Orm.Table("automate_goods ag").Select(fields)
	db.Joins("LEFT JOIN places p ON ag.place_id=p.id")
	db.Where("ag.id=?", id)
	db.Find(&dbInfo)

	return dingo.ErrorIf(db.Error, dbInfo)
}
